mysql> alter table stu modify birthday  date;
Query OK, 6 rows affected (0.63 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from stu;
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      | 2015-09-01 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           | 2015-10-01 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 2015-08-01 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      | NULL       | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      | NULL       | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      | NULL       | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
6 rows in set (0.00 sec)

mysql> alter table stu modify birthday  year;
Query OK, 6 rows affected, 3 warnings (0.48 sec)
Records: 6  Duplicates: 0  Warnings: 3

mysql> select * from stu;
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      |     0000 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           |     0000 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 |     0000 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      |     NULL | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      |     NULL | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      |     NULL | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
6 rows in set (0.00 sec)

mysql> update stu set birthday = '2015' where id =4;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu;
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      |     0000 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           |     0000 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 |     2015 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      |     NULL | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      |     NULL | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      |     NULL | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
6 rows in set (0.01 sec)

mysql> alter table stu modify birthday  time;
Query OK, 6 rows affected (0.52 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from stu;
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      | 00:00:00 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           | 00:00:00 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 00:20:15 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      | NULL     | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      | NULL     | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      | NULL     | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+----------------+----------+-----------+-------------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select curdate()
    -> ;
+------------+
| curdate()  |
+------------+
| 2016-06-22 |
+------------+
1 row in set (0.28 sec)

mysql> alter table stu modify birthday  date;
Query OK, 6 rows affected, 1 warning (0.75 sec)
Records: 6  Duplicates: 0  Warnings: 1

mysql> select * from stu;
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      | 2016-06-22 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           | 2016-06-22 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 2016-06-22 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      | NULL       | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      | NULL       | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      | NULL       | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
6 rows in set (0.02 sec)

mysql> update stu set birthday = curdate() where id=9;
Query OK, 1 row affected (0.34 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu;
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      | 2016-06-22 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           | 2016-06-22 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 2016-06-22 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      | 2016-06-22 | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      | NULL       | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      | NULL       | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select num();
ERROR 1305 (42000): FUNCTION school.num does not exist
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-06-22 08:56:12 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date(curdate());
+-----------------+
| date(curdate()) |
+-----------------+
| 2016-06-22      |
+-----------------+
1 row in set (0.00 sec)

mysql> select time(curdate());
+-----------------+
| time(curdate()) |
+-----------------+
| 00:00:00        |
+-----------------+
1 row in set (0.00 sec)

mysql> select time(now());
+-------------+
| time(now()) |
+-------------+
| 08:57:24    |
+-------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2016-06-22  |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            58 |
+---------------+
1 row in set (0.00 sec)

mysql> select * from stu;
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      | 1991-01-03 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           | 1989-01-03 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 1992-01-03 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      | 1995-01-03 | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      | 1988-01-03 | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      | 1994-01-03 | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select * from stu where sex='女' and birthday > '1990-01-01';
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby     | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
| 11 | 黄镇江 | 女  | 游戏,看书 | 1994-01-03 | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from stu order by birthday desc limit 1;
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby     | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
|  9 | 何子健 | 男  | 游戏,看书 | 1995-01-03 | 110112114 | 18925142345 |   20 | NULL  | NULL |
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from  stu;
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      | 1991-01-03 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           | 1989-01-03 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 1992-01-03 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      | 1995-01-03 | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      | 1988-01-03 | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      | 1994-01-03 | 110112114 | 18925142345 |   25 | NULL  | NULL |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
6 rows in set (0.00 sec)

mysql> sleect count(*) from  stu;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'sleect count(*) from  stu' at line 1
mysql> select count(*) from  stu;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.34 sec)

mysql> select count(id) from  stu;
+-----------+
| count(id) |
+-----------+
|         6 |
+-----------+
1 row in set (0.01 sec)

mysql> select max(age) max_age from stu;
+---------+
| max_age |
+---------+
|      25 |
+---------+
1 row in set (0.00 sec)

mysql> select min(age) max_age from stu;
+---------+
| max_age |
+---------+
|      16 |
+---------+
1 row in set (0.00 sec)

mysql> select * from  stu;
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
|  2 | 何子健 | 男  | 游戏,看书      | 1991-01-03 | 110112114 | 18925142345 |   20 | P2016 |    2 |
|  3 | 邓声明 | 男  | 游戏           | 1989-01-03 | 110112114 | 18925142345 |   16 | P2015 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 1992-01-03 | 110112114 | 18925142345 |   25 | P2015 |    2 |
|  9 | 何子健 | 男  | 游戏,看书      | 1995-01-03 | 110112114 | 18925142345 |   20 | NULL  | NULL |
| 10 | 邓声明 | 男  | 游戏,看书      | 1988-01-03 | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书      | 1994-01-03 | 110112114 | 18925142345 |   16 | NULL  | NULL |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select * from stu order by age desc limit 1;
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 1992-01-03 | 110112114 | 18925142345 |   25 | P2015 |    2 |
+----+--------+-----+----------------+------------+-----------+-------------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from stu order by age asc limit 1;
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby     | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
| 11 | 黄镇江 | 女  | 游戏,看书 | 1994-01-03 | 110112114 | 18925142345 |   16 | NULL  | NULL |
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from  stu where age = min(age);
ERROR 1111 (HY000): Invalid use of group function
mysql> select * from  stu where age =(select min(age) age from  stu);
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
| id | name   | sex | hobby     | birthday   | qq        | phone       | age  | class | num  |
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
|  3 | 邓声明 | 男  | 游戏      | 1989-01-03 | 110112114 | 18925142345 |   16 | P2015 |    1 |
| 10 | 邓声明 | 男  | 游戏,看书 | 1988-01-03 | 110112114 | 18925142345 |   16 | NULL  | NULL |
| 11 | 黄镇江 | 女  | 游戏,看书 | 1994-01-03 | 110112114 | 18925142345 |   16 | NULL  | NULL |
+----+--------+-----+-----------+------------+-----------+-------------+------+-------+------+
3 rows in set (0.00 sec)



mysql> select * from score;
+----+--------+-------+-------+
| id | name   | score | class |
+----+--------+-------+-------+
|  1 | 何子健 |    70 |  2015 |
|  2 | 邓声明 |    90 |  2016 |
|  3 | 黄镇江 |    85 |  2015 |
|  4 | 刘晓维 |    90 |  2016 |
+----+--------+-------+-------+
4 rows in set (0.00 sec)

mysql> select sum(score) from score where class=2016;
+------------+
| sum(score) |
+------------+
|        180 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(score) from score;
+------------+
| sum(score) |
+------------+
|        335 |
+------------+
1 row in set (0.02 sec)

mysql> select avg(score) from score;
+------------+
| avg(score) |
+------------+
|    83.7500 |
+------------+
1 row in set (0.00 sec)

mysql> select avg(score) from score where class=2016;
+------------+
| avg(score) |
+------------+
|    90.0000 |
+------------+
1 row in set (0.00 sec)

mysql> select avg(score) from score where class=2015;
+------------+
| avg(score) |
+------------+
|    77.5000 |
+------------+
1 row in set (0.00 sec)

mysql> select * from score;
+----+--------+-------+-------+
| id | name   | score | class |
+----+--------+-------+-------+
|  1 | 何子健 |    70 |  2015 |
|  2 | 邓声明 |    90 |  2016 |
|  3 | 黄镇江 |    85 |  2015 |
|  4 | 刘晓维 |    90 |  2016 |
+----+--------+-------+-------+
4 rows in set (0.00 sec)

mysql> select sum(score),avg(score),class from score group by class;
+------------+------------+-------+
| sum(score) | avg(score) | class |
+------------+------------+-------+
|        155 |    77.5000 |  2015 |
|        180 |    90.0000 |  2016 |
+------------+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from score;
+----+--------+-------+-------+
| id | name   | score | class |
+----+--------+-------+-------+
|  1 | 何子健 |    70 |  2015 |
|  2 | 邓声明 |    90 |  2016 |
|  3 | 黄镇江 |    85 |  2015 |
|  4 | 刘晓维 |    90 |  2016 |
|  5 | 肖飞翔 |   100 |  2017 |
+----+--------+-------+-------+
5 rows in set (0.00 sec)

mysql> select sum(score),avg(score),class from score group by class;
+------------+------------+-------+
| sum(score) | avg(score) | class |
+------------+------------+-------+
|        155 |    77.5000 |  2015 |
|        180 |    90.0000 |  2016 |
|        100 |   100.0000 |  2017 |
+------------+------------+-------+
3 rows in set (0.00 sec)

mysql> select sum(score),avg(score) avg_score,class from score group by class having avg_score>80;
+------------+-----------+-------+
| sum(score) | avg_score | class |
+------------+-----------+-------+
|        180 |   90.0000 |  2016 |
|        100 |  100.0000 |  2017 |
+------------+-----------+-------+
2 rows in set (0.00 sec)


mysql> select * from score;
+----+--------+-------+-------+
| id | name   | score | class |
+----+--------+-------+-------+
|  1 | 何子健 |    70 |  2015 |
|  2 | 邓声明 |    90 |  2016 |
|  3 | 黄镇江 |    85 |  2015 |
|  4 | 刘晓维 |    90 |  2016 |
|  5 | 肖飞翔 |   100 |  2017 |
+----+--------+-------+-------+
5 rows in set (0.00 sec)

mysql> select * from score;
+----+------+--------+-------+-------+
| id | sex  | name   | score | class |
+----+------+--------+-------+-------+
|  1 | 女   | 何子健 |    70 |  2015 |
|  2 | 男   | 邓声明 |    90 |  2016 |
|  3 | 女   | 黄镇江 |    85 |  2015 |
|  4 | 男   | 刘晓维 |    90 |  2016 |
|  5 | 女   | 肖飞翔 |   100 |  2017 |
|  6 | 女   | XX同学 |    70 |  2015 |
|  7 | 女   | XX同学 |    65 |  2016 |
|  8 | 女   | XX同学 |    89 |  2015 |
+----+------+--------+-------+-------+
8 rows in set (0.00 sec)

mysql> select avg(score) from  score group by sex;
+------------+
| avg(score) |
+------------+
|    79.8333 |
|    90.0000 |
+------------+
2 rows in set (0.00 sec)

mysql> select avg(score),sex from  score group by sex;
+------------+------+
| avg(score) | sex  |
+------------+------+
|    79.8333 | 女   |
|    90.0000 | 男   |
+------------+------+
2 rows in set (0.00 sec)

mysql> select avg(score),sex,class from  score group by sex,class;
+------------+------+-------+
| avg(score) | sex  | class |
+------------+------+-------+
|    78.5000 | 女   |  2015 |
|    65.0000 | 女   |  2016 |
|   100.0000 | 女   |  2017 |
|    90.0000 | 男   |  2016 |
+------------+------+-------+
4 rows in set (0.00 sec)

mysql>





mysql> select * from score;
+----+------+--------+-------+-------+
| id | sex  | name   | score | class |
+----+------+--------+-------+-------+
|  1 | 女   | 何子健 |    70 |  2015 |
|  2 | 男   | 邓声明 |    90 |  2016 |
|  3 | 女   | 黄镇江 |    85 |  2015 |
|  4 | 男   | 刘晓维 |    90 |  2016 |
|  5 | 女   | 肖飞翔 |   100 |  2017 |
|  6 | 女   | XX同学 |    70 |  2015 |
|  7 | 女   | XX同学 |    65 |  2016 |
|  8 | 女   | XX同学 |    89 |  2015 |
+----+------+--------+-------+-------+
8 rows in set (0.00 sec)

mysql> select * from stu;
+----+--------+-----+----------------+------------+-----------+-------------+------+-----+------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | cid | num  |
+----+--------+-----+----------------+------------+-----------+-------------+------+-----+------+
|  2 | 何子健 | 男  | 游戏,看书      | 1991-01-03 | 110112114 | 18925142345 |   20 |   1 |    2 |
|  3 | 邓声明 | 男  | 游戏           | 1989-01-03 | 110112114 | 18925142345 |   16 |   2 |    1 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 1992-01-03 | 110112114 | 18925142345 |   25 |   1 |    2 |
|  9 | 刘晓维 | 男  | 游戏,看书      | 1995-01-03 | 110112114 | 18925142345 |   20 |   2 |    1 |
| 10 | 邓小平 | 男  | 游戏,看书      | 1988-01-03 | 110112114 | 18925142345 |   16 |   3 |    2 |
| 11 | 黄大仙 | 女  | 游戏,看书      | 1994-01-03 | 110112114 | 18925142345 |   16 |   1 |    3 |
+----+--------+-----+----------------+------------+-----------+-------------+------+-----+------+
6 rows in set (0.00 sec)

mysql> select * from class;
+----+-----------+
| id | classname |
+----+-----------+
|  1 | PHP201601 |
|  2 | PHP201602 |
|  3 | PHP201603 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select * from  stu,class;
+----+--------+-----+----------------+------------+-----------+-------------+------+-----+------+---
-+-----------+
| id | name   | sex | hobby          | birthday   | qq        | phone       | age  | cid | num  | id
 | classname |
+----+--------+-----+----------------+------------+-----------+-------------+------+-----+------+---
-+-----------+
|  2 | 何子健 | 男  | 游戏,看书      | 1991-01-03 | 110112114 | 18925142345 |   20 |   1 |    2 |  1
 | PHP201601 |
|  2 | 何子健 | 男  | 游戏,看书      | 1991-01-03 | 110112114 | 18925142345 |   20 |   1 |    2 |  2
 | PHP201602 |
|  2 | 何子健 | 男  | 游戏,看书      | 1991-01-03 | 110112114 | 18925142345 |   20 |   1 |    2 |  3
 | PHP201603 |
|  3 | 邓声明 | 男  | 游戏           | 1989-01-03 | 110112114 | 18925142345 |   16 |   2 |    1 |  1
 | PHP201601 |
|  3 | 邓声明 | 男  | 游戏           | 1989-01-03 | 110112114 | 18925142345 |   16 |   2 |    1 |  2
 | PHP201602 |
|  3 | 邓声明 | 男  | 游戏           | 1989-01-03 | 110112114 | 18925142345 |   16 |   2 |    1 |  3
 | PHP201603 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 1992-01-03 | 110112114 | 18925142345 |   25 |   1 |    2 |  1
 | PHP201601 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 1992-01-03 | 110112114 | 18925142345 |   25 |   1 |    2 |  2
 | PHP201602 |
|  4 | 黄镇江 | 男  | 打架,游戏,看书 | 1992-01-03 | 110112114 | 18925142345 |   25 |   1 |    2 |  3
 | PHP201603 |
|  9 | 刘晓维 | 男  | 游戏,看书      | 1995-01-03 | 110112114 | 18925142345 |   20 |   2 |    1 |  1
 | PHP201601 |
|  9 | 刘晓维 | 男  | 游戏,看书      | 1995-01-03 | 110112114 | 18925142345 |   20 |   2 |    1 |  2
 | PHP201602 |
|  9 | 刘晓维 | 男  | 游戏,看书      | 1995-01-03 | 110112114 | 18925142345 |   20 |   2 |    1 |  3
 | PHP201603 |
| 10 | 邓小平 | 男  | 游戏,看书      | 1988-01-03 | 110112114 | 18925142345 |   16 |   3 |    2 |  1
 | PHP201601 |
| 10 | 邓小平 | 男  | 游戏,看书      | 1988-01-03 | 110112114 | 18925142345 |   16 |   3 |    2 |  2
 | PHP201602 |
| 10 | 邓小平 | 男  | 游戏,看书      | 1988-01-03 | 110112114 | 18925142345 |   16 |   3 |    2 |  3
 | PHP201603 |
| 11 | 黄大仙 | 女  | 游戏,看书      | 1994-01-03 | 110112114 | 18925142345 |   16 |   1 |    3 |  1
 | PHP201601 |
| 11 | 黄大仙 | 女  | 游戏,看书      | 1994-01-03 | 110112114 | 18925142345 |   16 |   1 |    3 |  2
 | PHP201602 |
| 11 | 黄大仙 | 女  | 游戏,看书      | 1994-01-03 | 110112114 | 18925142345 |   16 |   1 |    3 |  3
 | PHP201603 |
+----+--------+-----+----------------+------------+-----------+-------------+------+-----+------+---
-+-----------+
18 rows in set (0.00 sec)

mysql> select stu,name,stu.cid,class.classname from  stu,class;
ERROR 1054 (42S22): Unknown column 'stu' in 'field list'
mysql> select stu.name,stu.cid,class.classname from  stu,class;
+--------+-----+-----------+
| name   | cid | classname |
+--------+-----+-----------+
| 何子健 |   1 | PHP201601 |
| 何子健 |   1 | PHP201602 |
| 何子健 |   1 | PHP201603 |
| 邓声明 |   2 | PHP201601 |
| 邓声明 |   2 | PHP201602 |
| 邓声明 |   2 | PHP201603 |
| 黄镇江 |   1 | PHP201601 |
| 黄镇江 |   1 | PHP201602 |
| 黄镇江 |   1 | PHP201603 |
| 刘晓维 |   2 | PHP201601 |
| 刘晓维 |   2 | PHP201602 |
| 刘晓维 |   2 | PHP201603 |
| 邓小平 |   3 | PHP201601 |
| 邓小平 |   3 | PHP201602 |
| 邓小平 |   3 | PHP201603 |
| 黄大仙 |   1 | PHP201601 |
| 黄大仙 |   1 | PHP201602 |
| 黄大仙 |   1 | PHP201603 |
+--------+-----+-----------+
18 rows in set (0.00 sec)

mysql> select stu.name,stu.cid,class.id,class.classname from  stu,class;
+--------+-----+----+-----------+
| name   | cid | id | classname |
+--------+-----+----+-----------+
| 何子健 |   1 |  1 | PHP201601 |
| 何子健 |   1 |  2 | PHP201602 |
| 何子健 |   1 |  3 | PHP201603 |
| 邓声明 |   2 |  1 | PHP201601 |
| 邓声明 |   2 |  2 | PHP201602 |
| 邓声明 |   2 |  3 | PHP201603 |
| 黄镇江 |   1 |  1 | PHP201601 |
| 黄镇江 |   1 |  2 | PHP201602 |
| 黄镇江 |   1 |  3 | PHP201603 |
| 刘晓维 |   2 |  1 | PHP201601 |
| 刘晓维 |   2 |  2 | PHP201602 |
| 刘晓维 |   2 |  3 | PHP201603 |
| 邓小平 |   3 |  1 | PHP201601 |
| 邓小平 |   3 |  2 | PHP201602 |
| 邓小平 |   3 |  3 | PHP201603 |
| 黄大仙 |   1 |  1 | PHP201601 |
| 黄大仙 |   1 |  2 | PHP201602 |
| 黄大仙 |   1 |  3 | PHP201603 |
+--------+-----+----+-----------+
18 rows in set (0.00 sec)

mysql> select stu.name,stu.cid,class.id,class.classname from  stu,class where stu.cid=class.id;
+--------+-----+----+-----------+
| name   | cid | id | classname |
+--------+-----+----+-----------+
| 何子健 |   1 |  1 | PHP201601 |
| 邓声明 |   2 |  2 | PHP201602 |
| 黄镇江 |   1 |  1 | PHP201601 |
| 刘晓维 |   2 |  2 | PHP201602 |
| 邓小平 |   3 |  3 | PHP201603 |
| 黄大仙 |   1 |  1 | PHP201601 |
+--------+-----+----+-----------+
6 rows in set (0.01 sec)

mysql> select stu.name,stu.sex,stu,age,class.classname from  stu,class where stu.cid=class.id;
ERROR 1054 (42S22): Unknown column 'stu' in 'field list'
mysql> select stu.name,stu.sex,stu.age,class.classname from  stu,class where stu.cid=class.id;
+--------+-----+------+-----------+
| name   | sex | age  | classname |
+--------+-----+------+-----------+
| 何子健 | 男  |   20 | PHP201601 |
| 邓声明 | 男  |   16 | PHP201602 |
| 黄镇江 | 男  |   25 | PHP201601 |
| 刘晓维 | 男  |   20 | PHP201602 |
| 邓小平 | 男  |   16 | PHP201603 |
| 黄大仙 | 女  |   16 | PHP201601 |
+--------+-----+------+-----------+
6 rows in set (0.00 sec)

mysql> select t1.name,t1.sex,t1.age,t2.classname from  stu t1,class t2 where t1.cid=t2.id;
+--------+-----+------+-----------+
| name   | sex | age  | classname |
+--------+-----+------+-----------+
| 何子健 | 男  |   20 | PHP201601 |
| 邓声明 | 男  |   16 | PHP201602 |
| 黄镇江 | 男  |   25 | PHP201601 |
| 刘晓维 | 男  |   20 | PHP201602 |
| 邓小平 | 男  |   16 | PHP201603 |
| 黄大仙 | 女  |   16 | PHP201601 |
+--------+-----+------+-----------+
6 rows in set (0.00 sec)




mysql> select t1.name,t2.classname from stu t1 inner join  class t2 on t1.cid=t2.id;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 何子健 | PHP201601 |
| 邓声明 | PHP201602 |
| 黄镇江 | PHP201601 |
| 刘晓维 | PHP201602 |
| 邓小平 | PHP201603 |
| 黄大仙 | PHP201601 |
+--------+-----------+
6 rows in set (0.00 sec)

mysql> select t1.name,t2.classname from stu t1 inner join  class t2 on t1.cid=t2.id where t2.id=1;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 何子健 | PHP201601 |
| 黄镇江 | PHP201601 |
| 黄大仙 | PHP201601 |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> select t1.name,t2.classname from stu t1 inner join  class t2 on t1.cid=t2.id where t2.id=2\
ERROR:
Unknown command '\;'.
    ->     -> Ctrl-C -- exit!
l
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to you
MySQL server version for the right syntax to use near '\;

l' at line 1
mysql> select t1.name,t2.classname from stu t1 inner join  class t2 on t1.cid=t2.id where t2.id=2;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 邓声明 | PHP201602 |
| 刘晓维 | PHP201602 |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> select t1.name,t2.classname from stu t1 inner join  class t2 on t1.cid=t2.id where t2.id=3;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 邓小平 | PHP201603 |
+--------+-----------+
1 row in set (0.00 sec)

mysql> select t1.name,t2.classname from stu t1 inner join  class t2 on t1.cid=t2.id where t1.age>2

+--------+-----------+
| name   | classname |
+--------+-----------+
| 黄镇江 | PHP201601 |
+--------+-----------+
1 row in set (0.00 sec)

mysql> select t1.name,t2.classname from stu t1 inner join  class t2 on t1.cid=t2.id;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 何子健 | PHP201601 |
| 邓声明 | PHP201602 |
| 黄镇江 | PHP201601 |
| 刘晓维 | PHP201602 |
| 邓小平 | PHP201603 |
| 黄大仙 | PHP201601 |
+--------+-----------+
6 rows in set (0.00 sec)

mysql> select t1.name,t2.classname from stu t1 inner join  class t2 on t1.cid=t2.id;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 何子健 | PHP201601 |
| 邓声明 | PHP201602 |
| 黄镇江 | PHP201601 |
| 刘晓维 | PHP201602 |
| 邓小平 | PHP201603 |
+--------+-----------+
5 rows in set (0.00 sec)

mysql> select t1.name,t2.classname from stu t1 left join class t2 on t1.cid-t2.cid;
ERROR 1054 (42S22): Unknown column 't2.cid' in 'on clause'
mysql> select t1.name,t2.classname from stu t1 left join class t2 on t1.cid-t2.id;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`school`.`t1`.`cid` - `school`.`t2`
id`)'
mysql> select t1.name,t2.classname from stu t1 left join class t2 on t1.cid=t2.id;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 何子健 | PHP201601 |
| 黄镇江 | PHP201601 |
| 邓声明 | PHP201602 |
| 刘晓维 | PHP201602 |
| 邓小平 | PHP201603 |
| 黄大仙 | NULL      |
+--------+-----------+
6 rows in set (0.00 sec)

mysql> select t1.name,t2.classname from stu t1 right join class t2 on t1.cid=t2.id;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 何子健 | PHP201601 |
| 邓声明 | PHP201602 |
| 黄镇江 | PHP201601 |
| 刘晓维 | PHP201602 |
| 邓小平 | PHP201603 |
| NULL   | PHP201604 |
+--------+-----------+
6 rows in set (0.00 sec)

mysql> select t1.name,t2.classname from class t2 left join stu t1 on t1.cid=t2.id;
+--------+-----------+
| name   | classname |
+--------+-----------+
| 何子健 | PHP201601 |
| 邓声明 | PHP201602 |
| 黄镇江 | PHP201601 |
| 刘晓维 | PHP201602 |
| 邓小平 | PHP201603 |
| NULL   | PHP201604 |
+--------+-----------+
6 rows in set (0.00 sec)

mysql> select t1.id,t1.name,t2.uid,t2.coid from stu t1 left join coruse_stu t2 on t1.id=t2.uid;
ERROR 1146 (42S02): Table 'school.coruse_stu' doesn't exist
mysql> select t1.id,t1.name,t2.uid,t2.coid from stu t1 left join course_stu t2 on t1.id=t2.uid;
+----+--------+------+------+
| id | name   | uid  | coid |
+----+--------+------+------+
|  2 | 何子健 |    2 |    6 |
|  2 | 何子健 |    2 |    4 |
|  3 | 邓声明 |    3 |    3 |
|  3 | 邓声明 |    3 |    4 |
|  4 | 黄镇江 |    4 |    6 |
|  4 | 黄镇江 |    4 |    3 |
|  4 | 黄镇江 |    4 |    2 |
| 11 | 黄大仙 |   11 |    1 |
|  9 | 刘晓维 | NULL | NULL |
| 10 | 邓小平 | NULL | NULL |
+----+--------+------+------+
10 rows in set (0.00 sec)

mysql> select t1.id,t1.name,t2.uid,t2.coid,t3.id,t3.coursename from stu t1 left join course_stu t2 o
n t1.id=t2.uid left join course t3 on t2.coid=t3.id;
+----+--------+------+------+------+------------+
| id | name   | uid  | coid | id   | coursename |
+----+--------+------+------+------+------------+
|  2 | 何子健 |    2 |    6 |    6 | html       |
|  2 | 何子健 |    2 |    4 |    4 | C++        |
|  3 | 邓声明 |    3 |    3 |    3 | C#         |
|  3 | 邓声明 |    3 |    4 |    4 | C++        |
|  4 | 黄镇江 |    4 |    6 |    6 | html       |
|  4 | 黄镇江 |    4 |    3 |    3 | C#         |
|  4 | 黄镇江 |    4 |    2 |    2 | JAVA       |
| 11 | 黄大仙 |   11 |    1 |    1 | PHP        |
|  9 | 刘晓维 | NULL | NULL | NULL | NULL       |
| 10 | 邓小平 | NULL | NULL | NULL | NULL       |
+----+--------+------+------+------+------------+
10 rows in set (0.00 sec)

mysql> select t1.id,t1.name,t2.uid,t2.coid,t3.id,t3.coursename from stu t1 left join course_stu t2 o
n t1.id=t2.uid left join course t3 on t2.coid=t3.id where t3.coursename="html";
+----+--------+------+------+------+------------+
| id | name   | uid  | coid | id   | coursename |
+----+--------+------+------+------+------------+
|  2 | 何子健 |    2 |    6 |    6 | html       |
|  4 | 黄镇江 |    4 |    6 |    6 | html       |
+----+--------+------+------+------+------------+
2 rows in set (0.00 sec)

mysql> select * from stu left join class on stu.cid=class.id where class.id=2;
+----+--------+-----+-----------+------------+-----------+-------------+------+-----+------+----+---
--------+
| id | name   | sex | hobby     | birthday   | qq        | phone       | age  | cid | num  | id | cl
assname |
+----+--------+-----+-----------+------------+-----------+-------------+------+-----+------+----+---
--------+
|  3 | 邓声明 | 男  | 游戏      | 1989-01-03 | 110112114 | 18925142345 |   16 |   2 |    1 |  2 | PH
P201602 |
|  9 | 刘晓维 | 男  | 游戏,看书 | 1995-01-03 | 110112114 | 18925142345 |   20 |   2 |    1 |  2 | PH
P201602 |
+----+--------+-----+-----------+------------+-----------+-------------+------+-----+------+----+---
--------+
2 rows in set (0.00 sec)


mysql> select t1.id,t1.name,t2.uid,t2.coid from stu t1 left join coruse_stu t2 on t1.id=t2.uid;
ERROR 1146 (42S02): Table 'school.coruse_stu' doesn't exist
mysql> select t1.id,t1.name,t2.uid,t2.coid from stu t1 left join course_stu t2 on t1.id=t2.uid;
+----+--------+------+------+
| id | name   | uid  | coid |
+----+--------+------+------+
|  2 | 何子健 |    2 |    6 |
|  2 | 何子健 |    2 |    4 |
|  3 | 邓声明 |    3 |    3 |
|  3 | 邓声明 |    3 |    4 |
|  4 | 黄镇江 |    4 |    6 |
|  4 | 黄镇江 |    4 |    3 |
|  4 | 黄镇江 |    4 |    2 |
| 11 | 黄大仙 |   11 |    1 |
|  9 | 刘晓维 | NULL | NULL |
| 10 | 邓小平 | NULL | NULL |
+----+--------+------+------+
10 rows in set (0.00 sec)

mysql> select t1.id,t1.name,t2.uid,t2.coid,t3.id,t3.coursename from stu t1 left join course_stu t2 o
n t1.id=t2.uid left join course t3 on t2.coid=t3.id;
+----+--------+------+------+------+------------+
| id | name   | uid  | coid | id   | coursename |
+----+--------+------+------+------+------------+
|  2 | 何子健 |    2 |    6 |    6 | html       |
|  2 | 何子健 |    2 |    4 |    4 | C++        |
|  3 | 邓声明 |    3 |    3 |    3 | C#         |
|  3 | 邓声明 |    3 |    4 |    4 | C++        |
|  4 | 黄镇江 |    4 |    6 |    6 | html       |
|  4 | 黄镇江 |    4 |    3 |    3 | C#         |
|  4 | 黄镇江 |    4 |    2 |    2 | JAVA       |
| 11 | 黄大仙 |   11 |    1 |    1 | PHP        |
|  9 | 刘晓维 | NULL | NULL | NULL | NULL       |
| 10 | 邓小平 | NULL | NULL | NULL | NULL       |
+----+--------+------+------+------+------------+
10 rows in set (0.00 sec)

mysql> select t1.id,t1.name,t2.uid,t2.coid,t3.id,t3.coursename from stu t1 left join course_stu t2 o
n t1.id=t2.uid left join course t3 on t2.coid=t3.id where t3.coursename="html";
+----+--------+------+------+------+------------+
| id | name   | uid  | coid | id   | coursename |
+----+--------+------+------+------+------------+
|  2 | 何子健 |    2 |    6 |    6 | html       |
|  4 | 黄镇江 |    4 |    6 |    6 | html       |
+----+--------+------+------+------+------------+
2 rows in set (0.00 sec)

mysql> select * from stu left join class on stu.cid=class.id where class.id=2;
+----+--------+-----+-----------+------------+-----------+-------------+------+-----+------+----+---
--------+
| id | name   | sex | hobby     | birthday   | qq        | phone       | age  | cid | num  | id | cl
assname |
+----+--------+-----+-----------+------------+-----------+-------------+------+-----+------+----+---
--------+
|  3 | 邓声明 | 男  | 游戏      | 1989-01-03 | 110112114 | 18925142345 |   16 |   2 |    1 |  2 | PH
P201602 |
|  9 | 刘晓维 | 男  | 游戏,看书 | 1995-01-03 | 110112114 | 18925142345 |   20 |   2 |    1 |  2 | PH
P201602 |
+----+--------+-----+-----------+------------+-----------+-------------+------+-----+------+----+---
--------+
2 rows in set (0.00 sec)